/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package Model;

import Model.DBConnection;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

/**
 *
 * @author 013v
 */

public class ACManager {
     public int totalSales() {
        try {
            DBConnection db = new DBConnection();
            Connection conn = db.getConnection();
            int result = 0;
            
            PreparedStatement pstat = conn.prepareStatement("SELECT SUM(pr.ProductPrice * pur.Quantity) AS 'Total Sales' FROM product pr, purchaseditems pur WHERE pr.ProductID = pur.ProductID");
            ResultSet rs = pstat.executeQuery();

            while (rs.next()){
                result = rs.getInt("Total Sales");
            }
            conn.close();
            return result;
        
        } catch (SQLException ex) {
            ex.printStackTrace();
            return 0;
        }
    }
     
     public ResultSet productSales() {
        try {
            DBConnection db = new DBConnection();
            Connection conn = db.getConnection();
            
            PreparedStatement pstat = conn.prepareStatement("SELECT pr.ProductTitle AS 'Product Title', pur.Quantity AS 'Quantity', pr.ProductPrice * pur.Quantity AS 'Sales' FROM product pr, purchaseditems pur WHERE pr.ProductID = pur.ProductID GROUP BY pr.ProductID");
            ResultSet rs = pstat.executeQuery();
            
           // rs.next();
            return rs;
            
        } catch (SQLException ex) {
            ex.printStackTrace();
            return null;
        }
    }
     
     public ResultSet productTypeSales() {
        try {
            DBConnection db = new DBConnection();
            Connection conn = db.getConnection();
            
            PreparedStatement pstat = conn.prepareStatement("SELECT pt.ProductType, SUM(pur.Quantity) AS 'Quantity', SUM(pr.ProductPrice * pur.Quantity) AS 'Sales' FROM product pr, purchaseditems pur, producttype pt WHERE pr.ProductID = pur.ProductID AND pr.ProductType = pt.ProductTypeID GROUP BY pt.ProductType");
            ResultSet rs = pstat.executeQuery();

           // conn.close();
            return rs;
            
        } catch (SQLException ex) {
            ex.printStackTrace();
            return null;
        }
    }
}
     
